<?php

namespace app\shop\model;

use think\Db;
use app\crm\model\Sn;
use think\Exception;
use app\crm\model\Staff as StaffModel;
use app\shop\model\Client as ClientModel;
use app\shop\model\Clientbill as ClientbillModel;
use app\shop\model\ClientStaff as ClientStaffModel;
use app\shop\model\ClientCategory as ClientCategoryModel;
use app\shop\model\ClientLog as ClientLogModel;

class Client extends \ShopModel
{
    protected $table = 'sh_client';

    public function ClientDb(){
        return Db::name('client');
    }

    /**
     * 客户列表
     */
    public function search($params){

        $page = !empty($params['page']) ? $params['page'] : 1;
        $pageSize = !empty($params['pageSize']) ? $params['pageSize'] : 15;
        $where = [];
        $sort = [];

        //验证权限
        $utid = \request()->header('userId');
        $access = $this->getAccess($utid);
        if(!$access['hasrole']){
            return [
                'data' => [],
                'pager' => [
                    'page' => $page,
                    'pageSize' => $pageSize,
                    'total' => 0
                ]
            ];
        }

        if(!empty($params['sortable'])){
            foreach($params['sortable'] as &$s){
                $sort[$s['key']] = $s['rule'];
            }
        }

        if($access['is_manager'] === 0 && $access['power']['check_p'] === 1){
            $where['create_staff_id'] = $utid;
        }

        $where['is_del'] = 0;

        if(!empty($params['query'])){
            $where['client_name | mnemonic | keywords'] = ['like','%'.$params['query'].'%'];
        }

        if(!empty($params['create_staff'])){
            $where['create_staff'] = ['like','%'.$params['create_staff'].'%'];
        }

        if(!empty($params['level'])){
            $where['level_id'] = ['in',$params['level']];
        }

        if(!empty($params['region_id'])){
            $where['region_id'] = ['in',$params['region_id']];
        }

        if(!empty($params['business_type'])){
            $where['business_type'] = ['in',$params['business_type']];
        }

        if(!empty($params['source'])){
            $where['source'] = ['in',$params['source']];
        }

        if(!empty($params['establish_time'])){
            $where['establish_time'] = ['in',$params['establish_time']];
        }

        if(!empty($params['service_rate'])){
            $where['service_rate'] = ['in',$params['service_rate']];
        }
        
        if(!empty($params['deal_impression'])){
            $where['deal_impression'] = ['in',$params['deal_impression']];
        }

        if(!empty($params['enterprise_nature'])){
            $where['enterprise_nature'] = ['in',$params['enterprise_nature']];
        }

        if(!empty($params['year_turnover'])){
            $where['year_turnover'] = ['in',$params['year_turnover']];
        }

        if(!empty($params['enterprise_scale'])){
            $where['enterprise_scale'] = ['in',$params['enterprise_scale']];
        }

        if(!empty($params['area'])){
            $where['area'] = implode(',',$params['area']);
        }
        
        if(!empty($params['category'])){
            $ids = Db::name('client_category_relevance')->where('client_cate_id','in',$params['category'])->column('client_id');
            $where['client_id'] = ['in',$ids];
        }

        $not_in_client_ids = [];
        if(!empty($params['no_linkman'])){
            $not_in_client_ids = Db::name('client_staff_relevance')->group('client_id')->column('client_id');
        }

        if(!empty($params['no_documentary'])){
            $tmp = Db::name('client_documentary')->group('client_id')->column('client_id');
            $not_in_client_ids = array_unique(array_merge($not_in_client_ids,$tmp));
        }

        if(!empty($params['no_orderer'])){
            $tmp = Db::name('client_staff_relevance')->where('is_orderer',1)->group('client_id')->column('client_id');
            $not_in_client_ids = array_unique(array_merge($not_in_client_ids,$tmp));
        }

        $data = $this
        ->field('client_id,client_name,level_id,last_contract_staff,last_contract_time,create_staff_id,create_staff,update_at')
        ->where($where)
        ->where('client_id','not in',$not_in_client_ids)
        ->page($page,$pageSize)
        ->order($sort)
        ->select();

        $ClientStaffModel = new ClientStaffModel();
        $ClientStaffDb = $ClientStaffModel->ClientStaffDb();
        $ClientCategoryModel = new ClientCategoryModel();
        $ClientCategoryDb = $ClientCategoryModel->ClientCategoryDb();
        foreach($data as &$v){

            $v['level_name'] = Db::name('client_level')->where('level_id',$v['level_id'])->value('level_name');

            //联系人
            $linkman = $ClientStaffDb->alias('a')
            ->join('sh_crm_staff b','a.staff_id = b.staff_id')
            ->field('a.is_orderer,b.staff_id,b.staff_name,b.head_img,b.phone,b.qq,b.create_staff,b.attention_type,b.last_login')
            ->where('a.client_id',$v['client_id'])
            ->select();

            foreach($linkman as &$l){
                $l['tag'] = Db::name('crm_staff_tag_relevance')->alias('a')
                ->join('sh_tag b','a.tag_id = b.tag_id')
                ->where('a.staff_id',$l['staff_id'])
                ->value("GROUP_CONCAT(b.tag_name)");
            }

            $v['linkman'] = $linkman;

            //分类
            $category = $ClientCategoryDb->alias('a')
            ->join('sh_client_category b','a.client_cate_id = b.client_cate_id')
            ->where('a.client_id',$v['client_id'])
            ->value("GROUP_CONCAT(b.client_cate_name)");
            $v['category'] = $category;

            //最新跟单
            $documentary = Db::name('client_documentary')->where('client_id',$v['client_id'])->order('create_at desc')->field('documentary_id,title,stage_id,update_at')->find();
            if($documentary){
                $v['documentary_id'] = $documentary['documentary_id'];
                $v['title'] = $documentary['title'];
                $v['stage'] = Db::name('documentary_stage')->where('stage_id',$documentary['stage_id'])->value('stage');
                $v['no_change_days'] = ceil((time() - strtotime($documentary['update_at'])) / (3600 * 24)).'天';
            }else{
                $v['documentary_id'] = $documentary['documentary_id'];
                $v['title'] = '-';
                $v['stage'] = '-';
                $v['no_change_days'] = '-';
            }
            

            //处理数据
            if(!empty($v['last_contract_time'])){
                $v['no_contact_days'] = ceil((time() - strtotime($v['last_contract_time'])) / (3600 * 24)).'天';
            }
            
        }

        $total = $this->where($where)->field('client_id')->count();

        return [
            'data' => $data,
            'pager' => [
                'page' => $page,
                'pageSize' => $pageSize,
                'total' => $total
            ]
        ];

    }

    //获取权限
    public function getAccess($staff_id){
        $c=[];
        $is_admin = Db::name('crm_staff')->where('staff_id',$staff_id)->value('sys_custodian');
        if($is_admin){
            $c["hasrole"]=1;
            $c["is_manager"]=1;
            $c["area_list"]=[];
            $c["power"]=[];
        }else{
            $has=Db::name("crm_rule_staff")->where(["staff_id"=>$staff_id])->find();
            if($has){
                $c["hasrole"]=1;
                if($has["is_manager"]==1){
                    $c["is_manager"]=1;
                    $c["area_list"]=[];
                    $c["power"]=[];
                }else{
                    $c["is_manager"]=0;
                    $area_list=Db::name("crm_rule_area")->where(["staff_id"=>$staff_id])->value("area_list");
                    if($area_list){
                        $c["area_list"]=explode(',',$area_list);
                    }else{
                        $c["area_list"]=[];
                    }
                    $poweruser=Db::name("crm_rule_power_user")->where(["staff_id"=>$staff_id])->find();
                    if($poweruser){
                        if($poweruser["group_id"]){
                            $power=Db::name("crm_rule_power")->where(["obj_id"=>$poweruser["group_id"],"type"=>2])->field("check_p,add_p,update_p,delete_p,down_p,he_p,re_p,yaoqin_p")->find();
                        }else{
                            $power=Db::name("crm_rule_power")->where(["obj_id"=>$poweruser["staff_id"],"type"=>1])->field("check_p,add_p,update_p,delete_p,down_p,he_p,re_p,yaoqin_p")->find();
                        }
                        if(!$power){
                            $power=["check_p"=>0,"add_p"=>0,"update_p"=>0,"delete_p"=>0,"down_p"=>0,"he_p"=>0,"re_p"=>0,"yaoqin_p"=>0];
                        }
                    }else{
                        $power=["check_p"=>0,"add_p"=>0,"update_p"=>0,"delete_p"=>0,"down_p"=>0,"he_p"=>0,"re_p"=>0,"yaoqin_p"=>0];
                    }
                    $c["power"]=$power;
                }
            }else{
                $c["hasrole"]=0;
            }
        }
        return $c;

    }

    public function simpleClient($params){
        $where = [];
        $where['is_del'] = 0;
        if(!empty($params['query'])){
            $where['client_name'] = ['like','%'.$params['query'].'%'];
        }
        $data = $this->field('client_id,client_name')
        ->where($where)->select();

        foreach($data as &$v){
            //分类
            $category = Db::name('client_category_relevance')->alias('a')
            ->join('sh_client_category b','a.client_cate_id = b.client_cate_id')
            ->where('a.client_id',$v['client_id'])
            ->value("GROUP_CONCAT(b.client_cate_name)");
            $v['category'] = $category;
        }

        return $data;
    }

    //联系人列表
    public function searchStaff($params){

        $page = !empty($params['page']) ? $params['page'] : 1;
        $pageSize = !empty($params['pageSize']) ? $params['pageSize'] : 15;
        $where = [];
        $client_where = [];//筛选的客户条件

        $where['is_downstream'] = 0;

        $where['is_del'] = 0;

        if(!empty($params['query'])){
            $where['staff_name | phone | mnemonic'] = ['like','%'.$params['query'].'%'];
        }

        if(!empty($params['create_staff'])){
            $where['create_staff'] = ['like','%'.$params['create_staff'].'%'];
        }

        if(!empty($params['sex'])){
            $where['sex'] = ['in',$params['sex']];
        }

        if(!empty($params['is_audit'])){
            $where['is_audit'] = ['in',$params['is_audit']];
        }

        if(!empty($params['attention_type'])){
            $where['attention_type'] =  ['in',$params['attention_type']];
        }

        if(!empty($params['education_background'])){
            $where['education_background'] = ['in',$params['education_background']];
        }

        if(!empty($params['client'])){
            $client_where['client_name'] = ['like','%'.$params['client'].'%'];
        }

        if(!empty($params['region_id'])){
            $client_where['region_id'] = ['in',$params['region_id']];
        }

        $client_id_arr = [];

        if(!empty($params['category'])){
            $tmp_client = Db::name('client_category_relevance')
            ->where('client_cate_id','in',$params['category'])
            ->column('client_id');
            $client_id_arr = $tmp_client; 
        }

        if(!empty($params['client_id_arr'])){
            $client_id_arr = array_unique(array_merge($client_id_arr,$params['client_id_arr']));
        }

        if(!empty($client_id_arr)){
            $client_where['b.client_id'] = ['in',$client_id_arr];
        }

        $staff_ids = [];

        if(!empty($client_where)){
            $staff_ids[] = Db::name('client_staff_relevance')->alias('a')
            ->join('sh_client b','a.client_id = b.client_id')
            ->where($client_where)
            ->column('a.staff_id');
        }

        if(!empty($params['tag'])){
            $staff_ids[] = Db::name('crm_staff_tag_relevance')->alias('a')
            ->join('sh_crm_staff b','a.staff_id = b.staff_id')
            ->where('a.tag_id','in',$params['tag'])
            ->column('b.staff_id');
        }

        if(!empty($staff_ids)){
            $final_staff_ids = [];
            foreach($staff_ids as $s){
                $final_staff_ids = array_unique(array_merge($final_staff_ids,$s));
            }
            $where['staff_id'] = ['in',$final_staff_ids];
        }

        $not_in_staff_ids = [];

        if(!empty($params['no_client'])){
            $not_in_staff_ids = Db::name('client_staff_relevance')->group('staff_id')->column('staff_id');
        }
        
        if(!empty($params['birthday_soon'])){
            $where['DATEDIFF(birthday,CURDATE())'] = ['<=',30];
        }

        $data = Db::name('crm_staff')
        ->field('staff_id,staff_name,duty,phone,special_plane,create_staff,attention_type,is_audit,last_contract_staff,last_contract_time')
        ->where($where)
        ->where('staff_id','not in',$not_in_staff_ids)
        ->page($page,$pageSize)
        ->select();

        $ClientStaffModel = new ClientStaffModel();
        $ClientStaffDb = $ClientStaffModel->ClientStaffDb();
        $ClientCategoryModel = new ClientCategoryModel();
        $ClientCategoryDb = $ClientCategoryModel->ClientCategoryDb();
        foreach($data as &$v){

            $tag = Db::name('crm_staff_tag_relevance')->alias('a')
            ->join('sh_tag b','a.tag_id = b.tag_id')
            ->where('a.staff_id',$v['staff_id'])
            ->value("GROUP_CONCAT(b.tag_name)");

            $client_ids = $ClientStaffDb->where('staff_id',$v['staff_id'])->column('client_id');
            
            $client_arr = $this->where('client_id','in',$client_ids)->field("client_id,client_name")->select();
            
            $client = [];

            foreach($client_arr as $c){
                $client[] = $c['client_name'];
            }

            $category = $ClientCategoryDb->alias('a')
            ->join('sh_client_category b','a.client_cate_id = b.client_cate_id')
            ->where('a.client_id','in',$client_ids)
            ->value("GROUP_CONCAT(b.client_cate_name)");

            $v['client_arr'] = $client_arr;
            $v['client'] = !empty($client) ? implode(',',$client) : null;
            $v['category'] = $category;
            $v['tag'] = $tag;
            $v['is_checked'] = false;
        }

        $total = Db::name('crm_staff')->field('staff_id')->where($where)->count();

        return [
            'data' => $data,
            'pager' => [
                'page' => $page,
                'pageSize' => $pageSize,
                'total' => $total
            ]
        ];

    }

    public function simpleLinkman($params){
        $where = [];
        $where['is_downstream'] = 0;
        $where['is_del'] = 0;
        if(!empty($params['query'])){
            $where['staff_name | phone | mnemonic'] = ['like','%'.$params['query'].'%'];
        }
        if(!empty($params['client_id'])){
            $tmp = Db::name('client_staff_relevance')->where('client_id',$params['client_id'])->column('staff_id');
            $where['staff_id'] = ['in',$tmp];
        }
        $data = Db::name('crm_staff')->field('staff_id,staff_name,duty,head_img')
        ->where($where)->select();

        foreach($data as &$v){
            $client = Db::name('client_staff_relevance')->alias('a')
            ->join('sh_client b','a.client_id = b.client_id')
            ->where('a.staff_id',$v['staff_id'])
            ->value("GROUP_CONCAT(b.client_name)");
            $v['client'] = $client;
        }

        return $data;
    }

    public function getClientDetail($id){

        $utid = \request()->header('userId');
        
        $data = Db::name('client')->alias('a')
        ->join('sh_client_region b','a.region_id = b.region_id')
        ->field('a.*,b.region_name')
        ->where('client_id',$id)->find();

        $data['area'] = !empty($data['area']) ? explode(',',$data['area']) : [];

        //关联客户
        $data['relevance_client'] = Db::name('client_client_relevance')->alias('a')
        ->join('sh_client b','a.target_client_id = b.client_id')
        ->field('b.client_id,b.client_name')
        ->where('a.current_client_id',$id)->select();

        //分类
        $data['category'] = Db::name('client_category_relevance')->alias('a')
        ->join('sh_client_category b','a.client_cate_id = b.client_cate_id')
        ->where('a.client_id',$id)->column('b.client_cate_id');

        $data['category_name'] = Db::name('client_category_relevance')->alias('a')
        ->join('sh_client_category b','a.client_cate_id = b.client_cate_id')
        ->where('a.client_id',$id)->column('b.client_cate_name');

        //可查看的人
        $create = Db::name('crm_staff')->where('staff_id',$data['create_staff_id'])->field('staff_id,staff_name,head_img')->select();
        $can_see = $this->getCanseeStaff($create);
        $data['can_see'] = array_merge($can_see,$create);

        //联系人
        $data['linkman'] = Db::name('client_staff_relevance')->alias('a')
        ->join('sh_crm_staff b','a.staff_id = b.staff_id')
        ->where('b.is_downstream',0)
        ->where('a.client_id',$id)
        ->field('a.is_orderer,b.staff_id,b.staff_name,b.phone,b.special_plane,b.qq,b.attention_type')
        ->select();

        //最新跟单
        $data['documentary'] = Db::name('client_documentary')->where('client_id',$id)->order('create_at desc')->find();
        $data['documentary']['no_change_days'] = ceil((time() - strtotime($data['documentary']['update_at'])) / (3600 * 24));

        //发票
        $data['bill'] = Db::name('client_bill')->where('client_id',$id)->find();

        //图片
        $data['image'] = Db::name('client_img_relevance')->alias('a')
        ->join('sh_img b','a.img_id = b.img_id')
        ->where('a.client_id',$id)->field('b.img_id,b.img_url')->select();

        //附件
        $data['accessory'] = Db::name('client_accessory_relevance')->alias('a')
        ->join('sh_accessory b','a.accessory_id = b.accessory_id')
        ->where('a.client_id',$id)->field('b.accessory_id,b.accessory_url,b.accessory_name')->select();

        //日志
        $data['log'] = Db::name('client_log')
        ->field('operater,content,create_at')
        ->where('client_id',$id)
        ->order('create_at desc')
        ->select();

        return $data;
    }

    public function getCanseeStaff($staff=[]){
        $final_id = [];
        $staff = Db::name('crm_rule_staff')->select();
        foreach($staff as $v){
            if(!empty($staff_id) && $v['staff_id'] === $staff['staff_id']){
                continue;
            }
            $is_admin = Db::name('crm_staff')->where('staff_id',$v['staff_id'])->value('sys_custodian');
            if($is_admin){
                $final_id [] = $v['staff_id'];
                continue;
            }
            if($v['is_manager'] === 1){
                $final_id [] = $v['staff_id'];
            }else{
                $power_user = Db::name('crm_rule_power_user')->where('staff_id',$v['staff_id'])->find();
                if($power_user['group_id'] === 0){
                    $flag = Db::name('crm_rule_power')->where('type',1)->where('obj_id',$power_user['staff_id'])->value('check_p');
                }else{
                    $flag = Db::name('crm_rule_power')->where('type',2)->where('obj_id',$power_user['group_id'])->value('check_p');
                }
                if($flag === 2){
                    $final_id[] = $power_user['staff_id'];
                }
            }
        }
        
        $can_see = Db::name('crm_staff')->where('staff_id','in',$final_id)->where('is_del',0)->field('staff_id,staff_name,head_img')->select();

        return $can_see;

    }

    //修改客户
    public function updateClient($params){

        $utid = \request()->header('userId');
        $staff = Db::name('crm_staff')->where('staff_id',$utid)->value('staff_name');

        //验证权限
        $access = $this->getAccess($utid);

        if(!$access['hasrole']){
            throw Exception('无权修改');
        }

        if(!$access['is_manager'] && $access['power']['update_p'] === 1){
            $flag = Db::name('client')->where('client_id',$params['client']['client_id'])->value('create_staff_id');
            if($flag !== $utid){
                throw Exception('无权修改');
            }
        }

        //过滤不需要字段
        unset($params['client']['region_name']);
        unset($params['client']['category_name']);
        unset($params['client']['linkman']);
        unset($params['client']['documentary']);
        unset($params['client']['bill']);
        unset($params['client']['log']);
        unset($params['client']['image']);
        unset($params['client']['accessory']);
        unset($params['client']['relevance_client']);
        unset($params['client']['report']);
        unset($params['client']['can_see']);
        
        //客户分类
        Db::name('client_category_relevance')->where('client_id',$params['client']['client_id'])->delete();

        foreach($params['client']['category'] as $v){

            $tmp = ['client_id'=>$params['client']['client_id'],'client_cate_id'=>$v];

            Db::name('client_category_relevance')->insert($tmp);

        }

        unset($params['client']['category']);

        //主数据
        $params['client']['area'] = !empty($params['client']['area']) ? implode(',',$params['client']['area']) : null;
        $params['client']['update_staff'] = $staff;
        $params['client']['update_at'] = date('Y-m-d H:i:s');

        Db::name('client')->where('client_id',$params['client']['client_id'])->update($params['client']);

        //客户财务
        Db::name('client_bill')->where('client_id',$params['client']['client_id'])->update($params['bill']);

        //日志
        $log = [
            'client_id' => $params['client']['client_id'],
            'operater' => $staff,
            'content' => '修改客户数据',
            'create_at' => date('Y-m-d H:i:s')
        ];

        Db::name('client_log')->insert($log);

        return true;

    }

    //修改联系人
    public function updateStaff($params){

        $utid = \request()->header('userId');
        $staff = Db::name('crm_staff')->where('staff_id',$utid)->value('staff_name');

        $params['update_staff'] = $staff;
        $params['update_at'] = date('Y-m-d H:i:s');

        //过滤字段
        unset($params['client']);
        unset($params['log']);

        $tag = !empty($params['tag']) ? $params['tag'] : [];
        unset($params['tag']);

        Db::name('crm_staff')->where('staff_id',$params['staff_id'])->update($params);

        //标签
        Db::name('crm_staff_tag_relevance')->where('staff_id',$params['staff_id'])->delete();
        foreach($tag as $v){
            $tmp = [
                'staff_id' => $params['staff_id'],
                'tag_id' => $v
            ];
            Db::name('crm_staff_tag_relevance')->insert($tmp);
        }

        //日志
        $log = [
            'staff_id' => $params['staff_id'],
            'operater' => $staff,
            'content' => '修改客户数据',
            'create_at' => date('Y-m-d H:i:s')
        ];

        Db::name('crm_staff_log')->insert($log);

        return true;

    }

    public function getStaffDetail($id){

        $data = Db::name('crm_staff')
        ->field('staff_id,staff_name,native_place,hobby,email,head_img,duty,attention_type,phone,birthday,special_plane,wechat,micro_blog,qq,education_background,mnemonic,create_at,update_at,create_staff,update_staff,last_contract_staff,last_contract_time')
        ->where('staff_id',$id)
        ->find();

        //标签
        $data['tag'] = Db::name('crm_staff_tag_relevance')->where('staff_id',$id)->column('tag_id');

        //客户
        $data['client'] = Db::name('client_staff_relevance')->alias('a')
        ->join('sh_client b','a.client_id = b.client_id')
        ->where('a.staff_id',$id)
        ->field('b.client_id,b.client_name')
        ->select();
        
        //客户分类
        foreach($data['client'] as &$v){
            $v['category'] = Db::name('client_category_relevance')->alias('a')
            ->join('sh_client_category b','a.client_cate_id = b.client_cate_id')
            ->where('client_id',$v['client_id'])->column('b.client_cate_name');
        }

        //日志
        $data['log'] = Db::name('crm_staff_log')
        ->field('operater,content,create_at')
        ->where('staff_id',$id)
        ->select();

        return $data;
    }

    //直接新增客户
    public function addClient($params){

        $utid = \request()->header('userId');
        $params['area'] = !empty($params['area']) ? implode(',', $params['area']) : null;
        $params['create_staff_id'] = $utid;
        $params['create_staff'] = Db::name('crm_staff')->where('staff_id',$utid)->value('staff_name');

        $this->data($params)->allowField(true)->save();

        $params['client_id'] = $this->client_id;

        //客户分类
        foreach($params['category'] as $v){
            $tmp = ['client_id'=>$params['client_id'],'client_cate_id'=>$v];
            Db::name('client_category_relevance')->insert($tmp);
        }

        //客户财务
        $ClientbillModel = new ClientbillModel();
        $ClientbillModel->data($params)->allowField(true)->save();

        $client_id = $ClientbillModel->client_id;

        //保存关联
        Db::name('client_staff_relevance')->insert(['client_id'=>$params['client_id'],'staff_id'=>$params['staff_id']]);

        //日志
        $addLog = [
            'client_id' => $client_id,
            'operater' => $params['create_staff'],
            'content' => '创建客户',
            'create_at' => date('Y-m-d H:i:s')
        ];

        $updateLog = [
            'staff_id' => $params['staff_id'],
            'operater' => $params['create_staff'],
            'content' => '修改关联客户',
            'create_at' => date('Y-m-d H:i:s')
        ];

        Db::name('client_log')->insert($addLog);
        Db::name('crm_staff_log')->insert($updateLog);

        return true;

    }

    //关联现有客户
    public function relationClient($params){

        $utid = \request()->header('userId');
        $staff = Db::name('crm_staff')->where('staff_id',$utid)->value('staff_name');

        //关联
        foreach($params['client'] as $client){
            $tmp = [
                'staff_id' => $params['staff_id'],
                'client_id' => $client['client_id']
            ];
            $flag = Db::name('client_staff_relevance')->where($tmp)->value('client_id');
            if(!$flag){
                Db::name('client_staff_relevance')->insert($tmp);
            }
        }

        //日志
        $log = [
            'staff_id' => $params['staff_id'],
            'operater' => $staff,
            'content' => '修改关联客户',
            'create_at' => date('Y-m-d H:i:s')
        ];

        Db::name('crm_staff_log')->insert($log);

        //返回数据
        // $data = Db::name('client_staff_relevance')->alias('a')
        // ->join('client b','a.client_id = b.client_id')
        // ->field('b.client_id,b.client_name')
        // ->where('a.staff_id',$params['staff_id'])->select();

        // foreach($data as &$v){
        //     $v['category'] = Db::name('client_category_relevance')->alias('a')
        //     ->join('sh_client_category b','a.client_cate_id = b.client_cate_id')
        //     ->where('a.client_id',$v['client_id'])
        //     ->value("GROUP_CONCAT(b.client_cate_name)");
        // }

        return true;

    }

    //直接新增联系人
    public function addStaff($params){

        $utid = \request()->header('userId');
        $staff = Db::name('crm_staff')->where('staff_id',$utid)->value('staff_name');

        $params['create_staff'] = $staff;
        $params['update_staff'] = $staff;
        $params['last_contract_staff'] = $staff;
        $params['create_at'] = date('Y-m-d H:i:s');
        $params['update_at'] = date('Y-m-d H:i:s');
        $params['last_contract_time'] = date('Y-m-d H:i:s');

        $SN  = new Sn();
        $snS = $SN->getSn('YG');
        $params['staff_id'] = $snS['0'];

        $StaffModel = new StaffModel();
        $StaffModel->data($params)->allowField(true)->save();
        
        $staff_id = $StaffModel->staff_id;

        // $crm_index = new \app\crmindex\controller\Staff();
        // $crm_index->addxy($staff_id,$params['staff_name'],$params['phone'],$params['duty']);

        //标签
        if(!empty($params['tag'])){
            foreach($params['tag'] as $t){
                $tag_tmp = [
                    'tag_id' => $t,
                    'staff_id' => $staff_id
                ];
                Db::name('crm_staff_tag_relevance')->insert($tag_tmp);
            }   
        }

        //保存关联
        Db::name('client_staff_relevance')->insert(['client_id'=>$params['client_id'],'staff_id'=>$staff_id]);

        //日志
        $addLog = [
            'staff_id' => $staff_id,
            'operater' => $staff,
            'content' => '创建联系人',
            'create_at' => date('Y-m-d H:i:s')
        ];

        $updateLog = [
            'client_id' => $params['client_id'],
            'operater' => $params['create_staff'],
            'content' => '修改关联联系人',
            'create_at' => date('Y-m-d H:i:s')
        ];

        Db::name('crm_staff_log')->insert($addLog);
        Db::name('client_log')->insert($updateLog);
    }

    //关联现有联系人
    public function relationStaff($params){

        $utid = \request()->header('userId');
        $staff = Db::name('crm_staff')->where('staff_id',$utid)->value('staff_name');

        //关联
        foreach($params['staff'] as $staff){
            $tmp = [
                'client_id' => $params['client_id'],
                'staff_id' => $staff['staff_id']
            ];
            $flag = Db::name('client_staff_relevance')->where($tmp)->value('client_id');
            if(!$flag){
                Db::name('client_staff_relevance')->insert($tmp);
            }
        }

        //日志
        $log = [
            'client_id' => $params['client_id'],
            'operater' => $staff,
            'content' => '修改关联联系人',
            'create_at' => date('Y-m-d H:i:s')
        ];

        Db::name('client_log')->insert($log);

        //返回数据
        // $data = Db::name('client_staff_relevance')->alias('a')
        // ->join('sh_crm_staff b','a.staff_id = b.staff_id')
        // ->field('a.is_orderer,b.staff_id,b.staff_name,b.phone,b.special_plane,b.qq')
        // ->where('a.client_id',$params['client_id'])->select();
        
        return true;

    }

    //客户关联客户(直接新增)
    public function clientRelevanceAdd($params){

        $utid = \request()->header('userId');
        $params['area'] = !empty($params['area']) ? implode(',', $params['area']) : null;
        $params['create_staff_id'] = $utid;
        $params['create_staff'] = Db::name('crm_staff')->where('staff_id',$utid)->value('staff_name');

        $this->data($params)->allowField(true)->save();

        $client_id = $this->client_id;

        //客户分类
        foreach($params['category'] as $v){
            $tmp = ['client_id'=>$client_id,'client_cate_id'=>$v];
            Db::name('client_category_relevance')->insert($tmp);
        }

        //客户财务
        $ClientbillModel = new ClientbillModel();
        $ClientbillModel->data($params)->allowField(true)->save();

        //保存关联
        Db::name('client_client_relevance')->insert(['current_client_id'=>$params['current_client_id'],'target_client_id'=>$client_id]);
        Db::name('client_client_relevance')->insert(['target_client_id'=>$params['current_client_id'],'current_client_id'=>$client_id]);

        //日志
        $addLog = [
            'client_id' => $client_id,
            'operater' => $params['create_staff'],
            'content' => '创建客户',
            'create_at' => date('Y-m-d H:i:s')
        ];

        $updateLog = [
            'client_id' => $params['current_client_id'],
            'operater' => $params['create_staff'],
            'content' => '修改关联客户',
            'create_at' => date('Y-m-d H:i:s')
        ];

        Db::name('client_log')->insert($addLog);
        Db::name('client_log')->insert($updateLog);

        return true;
    }

    //客户关联客户(关联现有)
    public function clientRelevance($params){

        $utid = \request()->header('userId');
        $staff = Db::name('crm_staff')->where('staff_id',$utid)->value('staff_name');

        //关联
        foreach($params['target_client_ids'] as $target_client_id){
            $tmp = [
                'current_client_id' => $params['current_client_id'],
                'target_client_id' => $target_client_id
            ];
            $tmp2 = [
                'target_client_id' => $params['current_client_id'],
                'current_client_id' => $target_client_id
            ];
            $flag = Db::name('client_client_relevance')->where($tmp)->value('current_client_id');
            $flag2 = Db::name('client_client_relevance')->where($tmp2)->value('current_client_id');
            if(!$flag){
                Db::name('client_client_relevance')->insert($tmp);
            }
            if(!$flag2){
                Db::name('client_client_relevance')->insert($tmp2);
            }
        }
        
        //日志
        $log = [
            'client_id' => $params['current_client_id'],
            'operater' => $staff,
            'content' => '修改关联客户',
            'create_at' => date('Y-m-d H:i:s')
        ];

        Db::name('client_log')->insert($log);
    
        return true;

    }

    //删除客户
    public function delClient($id){
        
        $utid = \request()->header('userId');
        $staff_name = Db::name('crm_staff')->where('staff_id',$utid)->value('staff_name');

        //验证权限
        $access = $this->getAccess($utid);

        if(!$access['hasrole']){
            throw Exception('无权删除');
        }

        if(!$access['is_manager'] && $access['power']['delete_p'] === 1){
            $flag = Db::name('client')->where('client_id',$id)->value('create_staff_id');
            if($flag !== $utid){
                throw Exception('无权删除');
            }
        }

        //删除主数据
        $data = [
            'is_del' => 1,
            'del_staff' => $staff_name,
            'del_time' => date('Y-m-d H:i:s')
        ];

        Db::name('client')->where('client_id',$id)->update($data);

        return true;
    }

    //批量删除客户
    public function batchDel($client_ids){

        $utid = \request()->header('userId');
        $staff_name = Db::name('crm_staff')->where('staff_id',$utid)->value('staff_name');

        //验证权限
        $access = $this->getAccess($utid);

        if(!$access['hasrole']){
            throw Exception('无权删除');
        }

        $data = [
            'is_del' => 1,
            'del_staff' => $staff_name,
            'del_time' => date('Y-m-d H:i:s')
        ];
        if(!$access['is_manager'] && $access['power']['delete_p'] === 1){
            Db::name('client')->where('client_id','in',$client_ids)->where('create_staff_id',$utid)->update($data);
        }else{
            Db::name('client')->where('client_id','in',$client_ids)->update($data);
        }

        return true;
    }

    //删除全部客户
    public function delAll(){

        $utid = \request()->header('userId');
        $staff_name = Db::name('crm_staff')->where('staff_id',$utid)->value('staff_name');

        //验证权限
        $access = $this->getAccess($utid);

        if(!$access['hasrole']){
            throw Exception('无权删除');
        }

        $data = [
            'is_del' => 1,
            'del_staff' => $staff_name,
            'del_time' => date('Y-m-d H:i:s')
        ];
        if(!$access['is_manager'] && $access['power']['delete_p'] === 1){
            Db::name('client')->where('is_del',0)->where('create_staff_id',$utid)->update($data);
        }else{
            Db::name('client')->where('is_del',0)->update($data);
        }

        return true;
    }

    //删除联系人
    public function delStaff($id){

        $utid = \request()->header('userId');
        $staff_name = Db::name('crm_staff')->where('staff_id',$utid)->value('staff_name');

        //验证权限
        $access = $this->getAccess($utid);

        if(!$access['hasrole']){
            throw Exception('无权删除');
        }

        $data = [
            'is_del' => 1,
            'del_staff' => $staff_name,
            'del_time' => date('Y-m-d H:i:s')
        ];

        //删除主数据
        Db::name('crm_staff')->where('staff_id',$id)->update($data);

        // //取消关联客户
        // Db::name('client_staff_relevance')->where('staff_id',$id)->delete();

        // //删除标签
        // Db::name('crm_staff_tag_relevance')->where('staff_id',$id)->delete();

        return true;
    }

    //批量删除联系人
    public function batchDelStaff($staff_ids){

        $utid = \request()->header('userId');
        $staff_name = Db::name('crm_staff')->where('staff_id',$utid)->value('staff_name');

        //验证权限
        $access = $this->getAccess($utid);

        if(!$access['hasrole']){
            throw Exception('无权删除');
        }

        $data = [
            'is_del' => 1,
            'del_staff' => $staff_name,
            'del_time' => date('Y-m-d H:i:s')
        ];

        //删除主数据
        Db::name('crm_staff')->where('staff_id','in',$staff_ids)->update($data);

        return true;
    }

    //删除全部联系人
    public function delAllStaff(){

        $utid = \request()->header('userId');
        $staff_name = Db::name('crm_staff')->where('staff_id',$utid)->value('staff_name');

        //验证权限
        $access = $this->getAccess($utid);

        if(!$access['hasrole']){
            throw Exception('无权删除');
        }

        $data = [
            'is_del' => 1,
            'del_staff' => $staff_name,
            'del_time' => date('Y-m-d H:i:s')
        ];

        //删除主数据
        Db::name('crm_staff')->where('is_downstream',0)->where('is_del',0)->update($data);

        return true;

    }

    //解除关联(客户-联系人)
    public function removeRelevance($params){
        Db::name('client_staff_relevance')->where('client_id',$params['client_id'])->where('staff_id',$params['staff_id'])->delete();
        return true;
    }

    //修改订货人
    public function changeOrderer($params){
        Db::name('client_staff_relevance')->where('client_id',$params['client_id'])->where('staff_id',$params['staff_id'])->update(['is_orderer'=>$params['is_orderer']]);
        return true;
    }

    //解除关联客户(客户与客户)
    public function removeClient($params){
        Db::name('client_client_relevance')->where('current_client_id',$params['current_client_id'])->where('target_client_id',$params['target_client_id'])->delete();
        Db::name('client_client_relevance')->where('current_client_id',$params['target_client_id'])->where('target_client_id',$params['current_client_id'])->delete();
        return true;
    }

    //修改分类
    public function editCategory($params){

        $utid = \request()->header('userId');
        $staff = Db::name('crm_staff')->where('staff_id',$utid)->value('staff_name');

        //验证权限
        $access = $this->getAccess($utid);

        if(!$access['hasrole']){
            throw Exception('无权修改');
        }

        if(!$access['is_manager'] && $access['power']['update_p'] === 1){
            $flag = Db::name('client')->where('client_id',$params['client']['client_id'])->value('create_staff_id');
            if($flag !== $utid){
                throw Exception('无权修改');
            }
        }

        Db::name('client_category_relevance')->where('client_id',$params['client_id'])->delete();

        foreach($params['category'] as $v){

            $tmp = ['client_id'=>$params['client_id'],'client_cate_id'=>$v];

            Db::name('client_category_relevance')->insert($tmp);

        }

        $log = [
            'client_id' => $params['client_id'],
            'operater' => $staff,
            'content' => '修改客户分类',
            'create_at' => date('Y-m-d H:i:s')
        ];

        Db::name('client_log')->insert($log);

    }

    //改标签
    public function editTag($params){

        $utid = \request()->header('userId');
        $staff = Db::name('crm_staff')->where('staff_id',$utid)->value('staff_name');

        //验证权限
        $access = $this->getAccess($utid);

        if(!$access['hasrole']){
            throw Exception('无权修改');
        }

        if(!$access['is_manager'] && $access['power']['update_p'] === 1){
            $flag = Db::name('client')->where('client_id',$params['client']['client_id'])->value('create_staff_id');
            if($flag !== $utid){
                throw Exception('无权修改');
            }
        }

        Db::name('crm_staff_tag_relevance')->where('staff_id',$params['staff_id'])->delete();

        foreach($params['tag'] as $v){

            $tmp = ['staff_id'=>$params['staff_id'],'tag_id'=>$v];

            Db::name('crm_staff_tag_relevance')->insert($tmp);

        }

        $log = [
            'staff_id' => $params['staff_id'],
            'operater' => $staff,
            'content' => '修改联系人标签',
            'create_at' => date('Y-m-d H:i:s')
        ];

        Db::name('crm_staff_log')->insert($log);
    }

    //改销售区域
    public function editRegion($params){

        $utid = \request()->header('userId');
        $staff = Db::name('crm_staff')->where('staff_id',$utid)->value('staff_name');

        //验证权限
        $access = $this->getAccess($utid);

        if(!$access['hasrole']){
            throw Exception('无权修改');
        }

        if(!$access['is_manager'] && $access['power']['update_p'] === 1){
            $flag = Db::name('client')->where('client_id',$params['client']['client_id'])->value('create_staff_id');
            if($flag !== $utid){
                throw Exception('无权修改');
            }
        }

        Db::name('client')->where('client_id',$params['client_id'])->update(['region_id'=>$params['region_id']]);

        $log = [
            'client_id' => $params['client_id'],
            'operater' => $staff,
            'content' => '修改客户销售区域',
            'create_at' => date('Y-m-d H:i:s')
        ];

        Db::name('client_log')->insert($log);

    }

    //改创建人
    public function editCreateStaff($params){

        $utid = \request()->header('userId');
        $staff = Db::name('crm_staff')->where('staff_id',$utid)->value('staff_name');

        //验证权限
        //验证权限
        $access = $this->getAccess($utid);

        if(!$access['hasrole']){
            throw Exception('无权修改');
        }

        if(!$access['is_manager'] && $access['power']['update_p'] === 1){
            $flag = Db::name('client')->where('client_id',$params['client']['client_id'])->value('create_staff_id');
            if($flag !== $utid){
                throw Exception('无权修改');
            }
        }

        //修改创建人
        Db::name('client')->where('client_id',$params['client_id'])->update(['create_staff'=>$params['create_staff']]);

        //日志
        $log = [
            'client_id' => $params['client_id'],
            'operater' => $staff,
            'content' => '修改客户创建人',
            'create_at' => date('Y-m-d H:i:s')
        ];

        Db::name('client_log')->insert($log);

        if(isset($params['sync_linkman'])){
            $staff_ids = Db::name('client_staff_relevance')->where('client_id',$params['client_id'])->column('staff_id');

            Db::name('crm_staff')->where('staff_id','in',$staff_ids)->update(['create_staff'=>$params['create_staff']]); 

            foreach($staff_ids as $staff_id){
                $staff_log = [
                    'staff_id' => $staff_id,
                    'operater' => $staff,
                    'content' => '修改联系人创建人',
                    'create_at' => date('Y-m-d H:i:s')
                ];
                Db::name('crm_staff_log')->insert($staff_log);
            }
        }

    }

    //获取行业
    public function getTrade(){

        $trade = Db::name('client_trade')->select();

        $group = [];

        foreach($trade as $g){
            if(!$g['parent_id']){
                $group[] = $g;
            }
        }

        foreach($group as &$g){
            foreach($trade as $t){
                if($t['parent_id'] == $g['trade_id']){
                    $g['option'][] = $t;
                }
            }
        }

        return $group;

    }

    public function getCateAccess($staff_id){
        $department_id = Db::name('crm_staff')->where('staff_id',$staff_id)->value('department_id');
        $all_cate = Db::name('client_category')->select();
        $final = [];
        foreach($all_cate as $v){
            if($v['seetype'] === 0){
                $final[] = $v['client_cate_id'];
            }else{
                if($v["staff_list"]){
                    $st=explode(',',$v["staff_list"]);
                    if(in_array($staff_id,$st)){
                        $final[]=$v['client_cate_id'];
                    }
                }
                if($v["department_list"]){
                    $dl=explode(',',$v["department_list"]);
                    if(in_array($department_id,$dl)){
                        $final[]=$v['client_cate_id'];
                    }
                }
            }
        }

        return array_unique($final);
    }

    //获取客户搜索内容
    public function getClientSearchData($params){

        $utid = \request()->header('userId');
        $is_admin = Db::name('crm_staff')->where('staff_id',$utid)->value('sys_custodian');

        $final = [];

        //客户分类
        if($is_admin){
            $category = Db::name('client_category')->field('client_cate_id as id,client_cate_name as name')->select();
        }else{
            $cate_access = $this->getCateAccess($utid);
            if(empty($cate_access)){
                $category = [];
            }else{
                $category = Db::name('client_category')->where('client_cate_id','in',$cate_access)->field('client_cate_id as id,client_cate_name as name')->select();
            }
        }
        $final['category'] = [
            'type' => '客户分类',
            'is_show' => true,
            'required' => true,
            'multiple' => true,
            'list' => $category
        ];

        //销售区域
        if($is_admin){
            $region = Db::name('client_region')->field('region_id as id,region_name as name,parent_id as pid')->select();
        }else{
            $access = $this->getAccess($utid);
            if(!$access['hasrole']){
                $region = [];
            }else{
                if($access['is_manager']){
                    $region = Db::name('client_region')->field('region_id as id,region_name as name,parent_id as pid')->select();
                }else{
                    if(empty($access['area_list'])){
                        $region = [];
                    }else{
                        $region = Db::name('client_region')->where('region_id','in',$access['area_list'])->field('region_id as id,region_name as name,parent_id as pid')->select();
                    }
                }
            }
        }
        
        $region = $this->getRegionTree($region);

        $final['region_id'] = [
            'type' => '销售区域',
            'is_show' => true,
            'required' => true,
            'tree' => true,
            'list' => $region
        ];

        //客户等级
        $level = Db::name('client_level')->field('level_id as id,level_name as name')->select();
        $final['level_id'] = [
            'type' => '客户等级',
            'is_show' => false,
            'required' => true,
            'list' => $level
        ];

        //年营业额
        $final['year_turnover'] = [
            'type' => '年营业额',
            'is_show' => false,
            'list' => $this->getYearTurnover()
        ];

        //业务模式
        $final['business_type'] = [
            'type' => '业务模式',
            'is_show' => false,
            'list' => $this->getBusinessType()
        ];

        //企业性质
        $final['enterprise_nature'] = [
            'type' => '企业性质',
            'is_show' => false,
            'list' => $this->getEnterpriseNature()
        ];

        //客户来源
        $final['source'] = [
            'type' => '客户来源',
            'is_show' => false,
            'list' => $this->getSource()
        ];

        //成立时间
        $final['establish_time'] = [
            'type' => '成立时间',
            'is_show' => false,
            'list' => $this->getEstablishTime()
        ];

        //服务满意度
        $final['service_rate'] = [
            'type' => '服务满意度',
            'is_show' => false,
            'list' => $this->getServiceRate()
        ];

        //交易印象
        $final['deal_impression'] = [
            'type' => '交易印象',
            'is_show' => false,
            'list' => $this->getDealImpression()
        ];

        //企业规模
        $final['enterprise_scale'] = [
            'type' => '企业规模',
            'is_show' => false,
            'list' => $this->getEnterpriseScale()
        ];

        return $final;

    }   

    public function getRegionTree($data){
        //第一步 构造数据
        $items = array();
        foreach($data as $value){
            $items[$value['id']] = $value;
        }
        //第二部 遍历数据 生成树状结构
        $tree = array();
        foreach($items as $key => $value){
            if(isset($items[$value['pid']])){
                $items[$value['pid']]['child'][] = &$items[$key];
            }else{
                $tree[] = &$items[$key];
            }
        }
        return $tree;
    }

    //获取联系人搜索字段
    public function getStaffSearchData(){

        $utid = \request()->header('userId');

        $final = [];

        //联系人标签
        $tag = Db::name('tag')->field('tag_id as id,tag_name as name')->select();
        $final['tag'] = [
            'type' => '联系人标签',
            'multiple' => true,
            'is_show' => true,
            'list' => $tag
        ];

        //客户分类
        $category = Db::name('client_category')->field('client_cate_id as id,client_cate_name as name')->select();
        $final['category'] = [
            'type' => '客户分类',
            'is_show' => false,
            'list' => $category
        ];

        //销售区域
        $access = $this->getAccess($utid);
        if(!$access['hasrole']){
            $region = [];
        }else{
            if($access['is_manager']){
                $region = Db::name('client_region')->field('region_id as id,region_name as name')->select();
            }else{
                if(empty($access['area_list'])){
                    $region = [];
                }else{
                    $region = Db::name('client_region')->where('region_id','in',$access['area_list'])->field('region_id as id,region_name as name')->select();
                }
            }
        }
        $final['region_id'] = [
            'type' => '销售区域',
            'is_show' => false,
            'list' => $region
        ];

        //性别
        $final['sex'] = [
            'type' => '性别',
            'is_show' => false,
            'list' => [
                ['id' => 1,'name'=>'男'],
                ['id' => 0,'name'=>'女']
            ]
        ];

        //教育背景
        $final['education_background'] = [
            'type' => '教育背景',
            'is_show' => false,
            'list' => $this->geteDucationBackground()
        ];

        return $final;

    }

    //获取教育背景
    public function geteDucationBackground(){
        return array(
            ['id' => '小学及以下','name' => '小学及以下'],
            ['id' => '初中','name' => '初中'],
            ['id' => '高中','name' => '高中'],
            ['id' => '中专','name' => '中专'],
            ['id' => '大专','name' => '大专'],
            ['id' => '本科','name' => '本科'],
            ['id' => '研究生','name' => '研究生'],
            ['id' => '博士及以上','name' => '博士及以上']
        );
    }
    
    //获取年营业额
    public function getYearTurnover(){
        return array(
            ['id' => '100万以内','name' => '100万以内'],
            ['id' => '100万~1000万','name' => '100万~1000万'],
            ['id' => '1000万以上','name' => '1000万以上']
        );
    }

    //获取业务模式
    public function getBusinessType(){
        return array(
            ['id' => '生产加工','name' => '生产加工'],
            ['id' => '经销批发','name' => '经销批发'],
            ['id' => '招商代理','name' => '招商代理'],
            ['id' => '商业服务','name' => '商业服务'],
            ['id' => '其他','name' => '其他']
        );
    }

    //获取企业性质
    public function getEnterpriseNature(){
        return array(
            ['id' => '国有企业','name' => '国有企业'],
            ['id' => '中外合资企业','name' => '中外合资企业'],
            ['id' => '外商独资企业','name' => '外商独资企业'],
            ['id' => '私营企业','name' => '私营企业'],
            ['id' => '集体企业','name' => '集体企业'],
            ['id' => '政府机构','name' => '政府机构'],
            ['id' => '非盈利组织','name' => '非盈利组织'],
            ['id' => '其他','name' => '其他']
        );
    }
    
    //获取客户来源
    public function getSource(){
        return array(
            ['id' => '老客户介绍','name' => '老客户介绍'],
            ['id' => '电话来访','name' => '电话来访'],
            ['id' => '独立开发','name' => '独立开发'],
            ['id' => '媒体宣传','name' => '媒体宣传'],
            ['id' => '促销活动','name' => '促销活动'],
            ['id' => '代理商','name' => '代理商'],
            ['id' => '合作伙伴','name' => '合作伙伴'],
            ['id' => '会议','name' => '会议'],
            ['id' => '展会','name' => '展会'],
            ['id' => '网站','name' => '网站'],
            ['id' => '其他','name' => '其他']
        );
    }

    //获取成立时间
    public function getEstablishTime(){
        return array(
            ['id' => '2年以下','name' => '2年以下'],
            ['id' => '2-5年','name' => '2-5年'],
            ['id' => '5-10年','name' => '5-10年'],
            ['id' => '10年以上','name' => '10年以上']
        );
    }

    //获取服务满意度
    public function getServiceRate(){
        return array(
            ['id' => '非常满意','name' => '非常满意'],
            ['id' => '满意','name' => '满意'],
            ['id' => '一般','name' => '一般'],
            ['id' => '不满意','name' => '不满意']
        );
    }

    //获取交易印象
    public function getDealImpression(){
        return array(
            ['id' => '冲动买家','name' => '冲动买家'],
            ['id' => '大众买家','name' => '大众买家'],
            ['id' => '价格敏感','name' => '价格敏感'],
            ['id' => '理性买家','name' => '理性买家'],
            ['id' => '新潮时尚','name' => '新潮时尚'],
            ['id' => '质量挑剔','name' => '质量挑剔']
        );
    }

    //获取企业规模
    public function getEnterpriseScale(){
        return array(
            ['id' => '1-49人','name' => '1-49人'],
            ['id' => '50-99人','name' => '50-99人'],
            ['id' => '100-499人','name' => '100-499人'],
            ['id' => '500-999人','name' => '500-999人'],
            ['id' => '1000人以上','name' => '1000人以上']
        );
    }

    /**
     * 生成密码盐
     */
    public function generateSalt()
    {
        $str = '1234567890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ_';
        $strMaxIndex = strlen($str) - 1;
        $salt = '';
        $saltLenght = 6;
        for ($i = 0; $i < $saltLenght; $i ++) {
            $salt .= $str[mt_rand(0, $strMaxIndex)];
        }
        return $salt;
    }

    //保存关联图片
    public function saveImage($id,$url){
        $img_id = Db::name('img')->insertGetId(['img_url'=>$url]);
        Db::name('client_img_relevance')->insert(['client_id'=>$id,'img_id'=>$img_id]);
        return $img_id;
    }

    //删除关联图片
    public function removeImage($params){
        Db::name('client_img_relevance')->where($params)->delete();
        return true;
    }

    //保存关联附件
    public function saveAccessory($id,$file){
        $accessory_id = Db::name('accessory')->insertGetId(['accessory_url'=>$file['url'],'accessory_name'=>$file['name']]);
        Db::name('client_accessory_relevance')->insert(['client_id'=>$id,'accessory_id'=>$accessory_id]);
        return $accessory_id;
    }

    //删除关联附件
    public function removeAccessory($params){
        Db::name('client_accessory_relevance')->where($params)->delete();
        return true;
    }

    //导入客户数据
    public function importClient($data){

        $utid = \request()->header('userId');
        $utname = Db::name('crm_staff')->where('staff_id',$utid)->value('staff_name');

        //验证权限
        $access = $this->getAccess($utid);

        if(!$access['hasrole']){
            throw Exception('无权导入');
        }

        if(!$access['is_manager'] && $access['power']['down_p'] !== 1){
            throw Exception('无权导入');
        }

        $error = [];
        foreach($data as $k => &$v){
            //验证
            if(empty($v['client_name'])){
                $error[] = '第'.($k+1).'行客户名称不能为空';
                continue;
            }
            if(empty($v['level'])){
                $error[] = '第'.($k+1).'行客户等级不能为空';
                continue;
            }
            if(empty($v['category'])){
                $error[] = '第'.($k+1).'行客户分类不能为空';
                continue;
            }
            if(empty($v['region'])){
                $error[] = '第'.($k+1).'行销售区域不能为空';
                continue;
            }

            //客户名称
            $flag = Db::name('client')->where('client_name',$v['client_name'])->value('client_id');
            if($flag){
                $error[] = '第'.($k+1).'行客户名称已存在';
                continue;
            }

            //客户等级
            $level_id = Db::name('client_level')->where('level_name',$v['level'])->value('level_id');
            if($level_id){
                $v['level_id'] = $level_id;
            }else{
                $level_data = [
                    'level_name' => $v['level'],
                    'discount' => 100
                ];
                $v['level_id'] = Db::name('client_level')->insertGetId($level_data);
            }

            //销售区域
            $region_id = Db::name('client_region')->where('region_name',$v['region'])->value('region_id');
            if($region_id){
                $v['region_id'] = $region_id;
            }else{
                $region_data = [
                    'region_name' => $v['region'],
                    'parent_id' => 0,
                    'level' => 1
                ];
                $v['region_id'] = Db::name('client_region')->insertGetId($region_data);
            }

            $staff = $utname;
            $datetime = date('Y-m-d H:i:s');

            $v['create_at'] = $datetime;
            $v['update_at'] = $datetime;
            $v['last_contract_time'] = $datetime;

            $v['create_staff'] = $staff;
            $v['create_staff_id'] = $utid;
            $v['update_staff'] = $staff;
            $v['last_contract_staff'] = $staff;

            $this->data($v)->allowField(true)->isUpdate(false)->save();

            $client_id = $this->client_id;

            //日志
            $client_log = [
                'client_id' => $client_id,
                'operater' => $staff,
                'content' => '初始导入',
                'create_at' => $datetime
            ];

            Db::name('client_log')->insert($client_log);

            //客户分类
            $cate_name_arr = explode(',',$v['category']);
            foreach($cate_name_arr as $c){
                $cate_id = Db::name('client_category')->where('client_cate_name',$c)->value('client_cate_id');
                if(!$cate_id){
                    $cate_data = [
                        'client_cate_name' => $c
                    ];
                    $cate_id = Db::name('client_category')->insertGetId($cate_data);
                }
                $client_category_relevance = [
                    'client_id' => $client_id,
                    'client_cate_id' => $cate_id
                ];
                Db::name('client_category_relevance')->insert($client_category_relevance);
            }

            //客户发票数据
            $ClientbillModel = new ClientbillModel();
            $v['client_id'] = $client_id;
            $ClientbillModel->data($v)->allowField(true)->save();

            //联系人
            if(!empty($v['staff_name'])){

                $SN  = new Sn();
                $snS = $SN->getSn('YG');

                //联系人数据
                $staff_data = [
                    'staff_id' => $snS['0'],
                    'staff_name' => $v['staff_name'],
                    'duty' => $v['staff_duty'],
                    'phone' => $v['staff_phone'],
                    'email' => $v['staff_email'],
                    'special_plane' => $v['staff_special_plane'],
                    'qq' => $v['staff_qq'],
                    'create_at' => $datetime,
                    'update_at' => $datetime,
                    'last_contract_time' => $datetime,
                    'create_staff' => $staff,
                    'update_staff' => $staff,
                    'last_contract_staff' => $staff
                ];

                //联系人日志
                $staff_log = [
                    'staff_id' => $staff_data['staff_id'],
                    'operater' => $staff,
                    'content' => '初始导入',
                    'create_at' => $datetime
                ];

                Db::name('crm_staff')->insert($staff_data);

                Db::name('crm_staff_log')->insert($staff_log);

                //添加关联
                $client_staff_relevance = [
                    'client_id' => $client_id,
                    'staff_id' => $staff_data['staff_id']
                ];
                Db::name('client_staff_relevance')->insert($client_staff_relevance);

            }

        }

        return $error;
        
    }

    //回收站权限
    public function recycleAccess(){

        $utid = \request()->header('userId');

        $access = $this->getAccess($utid);

        if(!$access['hasrole']){
            return false;
        }

        if(!$access['is_manager'] && $access['power']['re_p'] !== 1){
            return false;
        }

        return true;

    }

    //回收站客户列表
    public function recycleClient($params){

        //验证权限
        $access = $this->recycleAccess();

        if(!$access){
            throw new Exception('无权访问');
        }

        $page = !empty($params['page']) ? $params['page'] : 1;
        $pageSize = !empty($params['pageSize']) ? $params['pageSize'] : 15;

        $where = [];

        if(!empty($params['query'])){
            $where['client_name'] = ['like','%'.$params['query'].'%'];
        }

        //总数
        $total = Db::name('client')->where($where)->where('is_del',1)->field('client_id')->count();

        //主数据
        $client = Db::name('client')->where($where)->where('is_del',1)->field('client_id,client_name,create_staff,create_at,del_staff,del_time')->page($page,$pageSize)->select();

        foreach($client as &$v){

            //分类
            $category = Db::name('client_category_relevance')->alias('a')
            ->join('sh_client_category b','a.client_cate_id = b.client_cate_id')
            ->where('a.client_id',$v['client_id'])
            ->value("GROUP_CONCAT(b.client_cate_name)");
            $v['category'] = $category;

        }

        return [
            'data' => $client,
            'pager' => [
                'page' => $page,
                'pageSize' => $pageSize,
                'total' => $total
            ]
        ];

    }

    //回收站联系人列表
    public function recycleStaff($params){
        
        //验证权限
        $access = $this->recycleAccess();

        if(!$access){
            throw new Exception('无权访问');
        }

        $page = !empty($params['page']) ? $params['page'] : 1;
        $pageSize = !empty($params['pageSize']) ? $params['pageSize'] : 15;

        $where = [];

        if(!empty($params['query'])){
            $where['staff_name | phone'] = ['like','%'.$params['query'].'%'];
        }

        //总数
        $total = Db::name('crm_staff')->where('is_downstream',0)->where('is_del',1)->field('staff_id')->count();

        //主数据
        $staff = Db::name('crm_staff')->where('is_downstream',0)->where('is_del',1)->field('staff_id,staff_name,duty,create_staff,create_at,del_staff,del_time')->page($page,$pageSize)->select();

        foreach($staff as &$v){

            //标签
            $tag = Db::name('crm_staff_tag_relevance')->alias('a')
            ->join('sh_tag b','a.tag_id = b.tag_id')
            ->where('a.staff_id',$v['staff_id'])
            ->value("GROUP_CONCAT(b.tag_name)");

            $client_ids = Db::name('client_staff_relevance')->where('staff_id',$v['staff_id'])->column('client_id');
            
            $client_arr = Db::name('client')->where('client_id','in',$client_ids)->field("client_id,client_name")->select();
            
            $client = [];

            foreach($client_arr as $c){
                $client[] = $c['client_name'];
            }

            $category = Db::name('client_category_relevance')->alias('a')
            ->join('sh_client_category b','a.client_cate_id = b.client_cate_id')
            ->where('a.client_id','in',$client_ids)
            ->value("GROUP_CONCAT(b.client_cate_name)");

            $v['client'] = !empty($client) ? implode(',',$client) : null;
            $v['category'] = $category;
            $v['tag'] = $tag;

        }

        return [
            'data' => $staff,
            'pager' => [
                'page' => $page,
                'pageSize' => $pageSize,
                'total' => $total
            ]
        ];

    }

    //彻底删除客户
    public function clientRemove($params){
        
        //验证权限
        $access = $this->recycleAccess();

        if(!$access){
            throw new Exception('无权访问');
        }

        $client_ids = $params['client_id'];
        
        Db::name('client')->where('client_id','in',$client_ids)->delete();

        //取消关联联系人
        Db::name('client_staff_relevance')->where('client_id','in',$client_ids)->delete();
        //取消关联客户
        Db::name('client_client_relevance')->where('current_client_id | target_client_id','in',$client_ids)->delete();
        //删除关联分类
        Db::name('client_category_relevance')->where('client_id','in',$client_ids)->delete();
        //删除发票信息
        Db::name('client_bill')->where('client_id','in',$client_ids)->delete();
        //删除日志信息
        Db::name('client_log')->where('client_id','in',$client_ids)->delete();

        return true;
    }

    //彻底删除联系人
    public function staffRemove($params){

        //验证权限
        $access = $this->recycleAccess();

        if(!$access){
            throw new Exception('无权访问');
        }

        $staff_ids = $params['staff_id'];
        
        //删除主数据
        Db::name('crm_staff')->where('staff_id','in',$staff_ids)->delete();

        //取消关联客户
        Db::name('client_staff_relevance')->where('staff_id','in',$staff_ids)->delete();

        //删除标签
        Db::name('crm_staff_tag_relevance')->where('staff_id','in',$staff_ids)->delete();

    }

    //还原客户
    public function clientRecover($params){

        //验证权限
        $access = $this->recycleAccess();

        if(!$access){
            throw new Exception('无权访问');
        }

        $client_ids = $params['client_id'];

        Db::name('client')->where('client_id','in',$client_ids)->update(['is_del'=>0]);

        return true;

    }

    //还原联系人
    public function staffRecover($params){
        //验证权限
        $access = $this->recycleAccess();

        if(!$access){
            throw new Exception('无权访问');
        }

        $staff_ids = $params['staff_id'];

        Db::name('crm_staff')->where('staff_id','in',$staff_ids)->update(['is_del'=>0]);

        return true;
    }

    //清空删除客户
    public function clientTruncate(){

        //验证权限
        $access = $this->recycleAccess();

        if(!$access){
            throw new Exception('无权访问');
        }

        $client_ids = Db::name('client')->where('is_del',1)->column('client_id');

        Db::name('client')->where('client_id','in',$client_ids)->delete();

        //取消关联联系人
        Db::name('client_staff_relevance')->where('client_id','in',$client_ids)->delete();
        //取消关联客户
        Db::name('client_client_relevance')->where('current_client_id | target_client_id','in',$client_ids)->delete();
        //删除关联分类
        Db::name('client_category_relevance')->where('client_id','in',$client_ids)->delete();
        //删除发票信息
        Db::name('client_bill')->where('client_id','in',$client_ids)->delete();
        //删除日志信息
        Db::name('client_log')->where('client_id','in',$client_ids)->delete();

        return true;

    }

    //清空删除联系人
    public function staffTruncate(){

        //验证权限
        $access = $this->recycleAccess();

        if(!$access){
            throw new Exception('无权访问');
        }

        $staff_ids = Db::name('crm_staff')->where('is_del',1)->column('staff_id');

        Db::name('crm_staff')->where('staff_id','in',$staff_ids)->delete();

        //取消关联客户
        Db::name('client_staff_relevance')->where('staff_id','in',$staff_ids)->delete();

        //删除标签
        Db::name('crm_staff_tag_relevance')->where('staff_id','in',$staff_ids)->delete();

        return true;

    }

}
